﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;

namespace OracleDAL.RunReport
{
    public  class ReportManager
    {
        public DataSet getStatisticItem()
        {
            string strSql = "select   itemid, itemname,isvisible, matchprocname,databasekind from reportstatisticitem where isvisible='1'";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 获取配置表字段
        /// </summary>
        /// <param name="procid"></param>
        /// <returns></returns>
        public DataSet getProcConfig(string procid)
        {
            string strSql = "select ITEMID,PROCENAME,PROCCNAME,MATCHFIELD from reportstatisticitconfig where ITEMID='" + procid + "'";
            return DBUtility.OracleHelper.Query(strSql);
        }
        public int excutSql(string sql)
        {
            return DBUtility.OracleHelper.ExecuteSql(sql);
        }
        public DataSet getStatistic(string strWhere)
        {
            string strSql = "select STATISTICID,STATISTICNAME,STARTTIME,ENDTIME from reportstatistic where " + strWhere + "";
            return DBUtility.OracleHelper.Query(strSql);
        }
        public void delReport(string id)
        {
            string strSql = "declare begin delete reportstatistic where STATISTICID='" + id + "';delete reportresultdetail where STATISTICID='" + id + "'; end; ";
            DBUtility.OracleHelper.ExecuteSql(strSql);
        }
        /// <summary>
        /// 获得已经选中的统计指标
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string getStatisticItem(string id)
        {
            string item = "";
            if (id != "")
            {
                string strSql = "select distinct itemid from reportresultdetail  where statisticid='" + id + "' ";
                OracleDataReader dr = DBUtility.OracleHelper.ExecuteReader(strSql);
                while (dr.Read())
                {
                    item += dr["itemid"].ToString() + ",";
                }
                dr.Close();
                dr.Dispose();
            }
            return item;
        }
        public string getStatisticItemConMy(string id)
        {
            string item = "";
            if (id != "")
            {
                string strSql = "select distinct  ITEMID from REPORTRESULTDETAIL  where   STATISTICID='" + id + "' ";
                MySqlDataReader dr = DBUtility.MySQLHelper.ExecuteReader(CommandType.Text, strSql);


                while (dr.Read())
                {
                    item += dr["ITEMID"].ToString() + ",";
                }
                dr.Close();
                dr.Dispose();
            }
            return item;
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet getStatisticDetail(string id)
        {
            string strSql = "select STATISTICID,ITEMID,FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD8,FIELD9,FIELD10 from reportresultdetail  where statisticid='" + id + "' ";
            return DBUtility.OracleHelper.Query(strSql);
        
        }
        public DataSet getConfigAll()
        {
            string strSql = "select ITEMID,PROCENAME,PROCCNAME,MATCHFIELD from reportstatisticitconfig";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="statisticID"></param>
        /// <param name="itemIdCon">itemid 和 数据库类型</param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public int excutProc(string statisticID, string itemIdCon, string starttime, string endtime)
        {
         

            string[] itemid = itemIdCon.Split('|');
            if (itemid[1] == "oracle")
            {
                   OracleParameter[] parameters = {
                    new OracleParameter("rid", OracleType.VarChar, 64),
                    new OracleParameter("pid", OracleType.VarChar, 64),
                    //new OracleParameter("stime", OracleType.VarChar,64),
                    //new OracleParameter("etime", OracleType.VarChar,64),
                    new OracleParameter("stime", OracleType.DateTime),
                    new OracleParameter("etime", OracleType.DateTime),
                    };
                   parameters[0].Value = statisticID;
                   parameters[1].Value = itemid[0];
                   //parameters[2].Value = string.Format ( "{0:G}",starttime);
                   //parameters[3].Value = string.Format("{0:G}", endtime);

                   parameters[2].Value = Convert.ToDateTime(starttime);//string.Format("{0:G}", starttime);
                   parameters[3].Value = Convert.ToDateTime(endtime);// string.Format("{0:G}", endtime);

                   parameters[0].Direction = ParameterDirection.Input;
                   parameters[1].Direction = ParameterDirection.Input;
                   parameters[2].Direction = ParameterDirection.Input;
                   parameters[3].Direction = ParameterDirection.Input;

               int a= DBUtility.OracleHelper.RunProcedure(itemid[2], parameters);
            }
            else
            {
                MySqlParameter[] parameters ={
                    new MySqlParameter("rid", MySqlDbType.VarChar, 64),
                    new MySqlParameter("pid", MySqlDbType.VarChar, 64),
                    new MySqlParameter("stime", MySqlDbType.VarChar,64),
                    new MySqlParameter("etime", MySqlDbType.VarChar,64),
                                             };
                   parameters[0].Value = statisticID;
                   parameters[1].Value = itemid[0];
                   parameters[2].Value = starttime;
                   parameters[3].Value = endtime;

               int a=    DBUtility.MySQLHelper.ExecuteNonQuery(CommandType.StoredProcedure, itemid[2], parameters);
            }
            return 1;
        }
        /// <summary>
        /// 获取默认开始时间
        /// </summary>
        public DataSet  getdefaultStartTime()
        {
            string strSql = "select  to_char(endtime,'yyyy-mm-dd hh24:mi:ss')  from reportstatistic order by  endtime desc ";
            return DBUtility.OracleHelper.Query(strSql);
        }
    }
}
